﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Drawing;

public partial class Whir_System_Module_Excel_out : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string strWhere = "";
            DataSet ds = Whir.SQLServerDAL.DbHelperSQL.Query(GetSql() + " where 1=1 " + strWhere + " order by CreateDate desc");
            DataTable2Excel(ds.Tables[0]);

            //if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            //{
            //    DataTable dtNew = new DataTable();
            //    dtNew.Columns.Add("会员ID", typeof(string));
            //    dtNew.Columns.Add("会员名", typeof(string));
            //    dtNew.Columns.Add("会员类别", typeof(string));
            //    dtNew.Columns.Add("真实姓名", typeof(string));
            //    dtNew.Columns.Add("性别", typeof(string));
            //    dtNew.Columns.Add("出生日期", typeof(string));
            //    dtNew.Columns.Add("电子邮箱", typeof(string));
            //    dtNew.Columns.Add("MSN/QQ", typeof(string));
            //    dtNew.Columns.Add("电话/手机", typeof(string));
            //    dtNew.Columns.Add("所属省", typeof(string));
            //    dtNew.Columns.Add("所属市", typeof(string));
            //    dtNew.Columns.Add("所属区", typeof(string));
            //    dtNew.Columns.Add("联系地址", typeof(string));
            //    dtNew.Columns.Add("积分", typeof(string));
            //    dtNew.Columns.Add("申请时间", typeof(string));
            //    DataRow rowNew = dtNew.NewRow();

            //    DataTable dt = ds.Tables[0];
            //    foreach (DataRow row in dt.Rows)
            //    {
            //        rowNew["会员ID"] = row[0];
            //        rowNew["会员名"] = row[1];
            //        rowNew["会员类别"] = row[2];
            //        rowNew["真实姓名"] = row[3];
            //        rowNew["性别"] = row[4];

            //        if (row[5] != null)
            //        {
            //            if (!string.IsNullOrEmpty(row[5].ToString()))
            //            {
            //                rowNew["出生日期"] = DateTime.Parse(row[5].ToString()).ToShortDateString();
            //            }
            //            else
            //                rowNew["出生日期"] = "";
            //        }
            //        else
            //        {
            //            rowNew["出生日期"] = "";
            //        }

            //        rowNew["电子邮箱"] = row[6];
            //        rowNew["MSN/QQ"] = row[7];
            //        rowNew["电话/手机"] = row[8];
            //        rowNew["所属省"] = WebHelper.GetArea(row[9].ToString(), 0);
            //        rowNew["所属市"] = WebHelper.GetArea(row[9].ToString(), 1);
            //        rowNew["所属区"] = WebHelper.GetArea(row[9].ToString(), 2);
            //        rowNew["联系地址"] = row[10];
            //        rowNew["积分"] = row[11];
            //        rowNew["申请时间"] = row[12];

            //        dtNew.Rows.Add(rowNew);
            //        rowNew = dtNew.NewRow();
            //    }

            //    DataTable2Excel(dtNew);
            //}
            //else
            //{
            //    DataTable2Excel(ds.Tables[0]);
            //}
        }
    }

    #region 导出EXCEL格式方法
    /// <summary>
    /// Sql语句
    /// </summary>
    /// <returns></returns>
    protected string GetSql()
    {
        string sql =
        "SELECT" +
        " Whir_U_Users_PID as '会员ID'," +
        " LoginName as '用户名'," +
        " CompanyAddress as '公司名称'," +
        " TrueName as '联系人'," +
        " LinkTel as '联系电话'," +
        " IsCheck as '审核状态'," +
        " CreateDate as '注册时间'" +
        " FROM Whir_U_Users ";

        return sql;
    }
    /// <summary>
    /// 输出EXCEL数据
    /// </summary>
    /// <param name="dtData"></param>
    public static void DataTable2Excel(System.Data.DataTable dtData)
    {
        System.Web.UI.WebControls.DataGrid dgExport = null;
        // 当前对话 
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
        // IO用于导出并返回excel文件 
        System.IO.StringWriter strWriter = null;
        System.Web.UI.HtmlTextWriter htmlWriter = null;

        if (dtData != null)
        {
            // 设置编码和附件格式 
            //Response.Charset = "UTF-8";
            //Response.ContentEncoding = System.Test.Encoding.GetEncoding("UTF-8");

            curContext.Response.ContentType = "application/vnd.ms-excel";
            //curContext.Response.Charset = "UTF-8";
            curContext.Response.ContentEncoding = System.Text.Encoding.Default;
            curContext.Response.Charset = "";
            curContext.Response.AddHeader("content-disposition", "attachment; filename=User" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls");

            // 导出excel文件 
            strWriter = new System.IO.StringWriter();
            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

            // 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid 
            dgExport = new System.Web.UI.WebControls.DataGrid();
            dgExport = SetExcelStyle(dgExport);
            dgExport.DataSource = dtData.DefaultView;

            dgExport.AllowPaging = false;
            dgExport.DataBind();

            // 返回客户端 
            dgExport.RenderControl(htmlWriter);
            curContext.Response.Write(strWriter.ToString());
            curContext.Response.End();
        }
    }
    /// <summary>
    /// 设置EXCEL格式
    /// </summary>
    /// <param name="m_grid"></param>
    /// <returns></returns>
    public static DataGrid SetExcelStyle(DataGrid m_grid)
    {
        m_grid.Style.Add("font-size", "9pt");
        m_grid.Style.Add("font-family", "Courier New");
        m_grid.Style.Add("heigth", "25px");
        m_grid.HeaderStyle.Height = Unit.Pixel(25);

        m_grid.GridLines = GridLines.Both;
        m_grid.BorderStyle = BorderStyle.Solid;
        m_grid.BorderColor = Color.FromKnownColor(KnownColor.Gray);
        m_grid.ItemStyle.BackColor = Color.FromKnownColor(KnownColor.White);
        m_grid.FooterStyle.BackColor = Color.FromKnownColor(KnownColor.Beige);
        m_grid.HeaderStyle.BackColor = Color.FromKnownColor(KnownColor.Beige);
        m_grid.AlternatingItemStyle.BackColor = Color.FromKnownColor(KnownColor.White);

        return m_grid;
    }
    #endregion
}
